package com.food.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.FoodsearchDAO;
import com.food.model.Foodsave;
import com.food.model.Foodsearch;

public class FoodsearchDAOJdbc implements FoodsearchDAO {
	private DataSource ds = null;
	public FoodsearchDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}
	private static final String INSERT ="insert into foodsearch (fcid, fsname) values(?,?)";
	@Override
	public void insertFoodsearch(Foodsearch bean) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(INSERT);
			pstmt.setString(1, bean.getFcid());
			pstmt.setString(2, bean.getFsname());
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	private static final String UPDATE ="update foodsearch set fcid=?, fsname=? where fsid =?";
	@Override
	public void updateFoodsearch(String fcid, String fsname, int fsid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(UPDATE);
			pstmt.setString(1, fcid);
			pstmt.setString(2, fsname);
			pstmt.setInt(3, fsid);
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}
	private static final String DELETE ="delete from foodsearch where fsid =?";
	@Override
	public void deleteFoodsearch(int fsid) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(DELETE);
			pstmt.setInt(1, fsid);
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}


	}
	private static final String SELECTByID ="select fsid,fcid,fsname from foodsearch where fsid=?";
	@Override
	public Foodsearch findFoodsearchByID(int fsid) {
		Foodsearch result = null;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTByID);
			pstmt.setInt(1, fsid);
			rs = pstmt.executeQuery();

			while(rs.next()){
				result = new Foodsearch();
				result.setFsid(rs.getInt("fsid"));
				result.setFcid(rs.getString("fcid"));
				result.setFsname(rs.getString("fsname"));
			
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
	private static final String SELECTByFoodcategory ="select fsid,fcid,fsname from foodsearch where fcid=?";
	@Override
	public List<Foodsearch> findFoodsearchByFoodcategory(String fcid) {
		List<Foodsearch> result = null;
		Foodsearch bean = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
//			conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			pstmt = conn.prepareStatement(SELECTByID);
			pstmt.setString(1, fcid);
			rs = pstmt.executeQuery();
			result = new ArrayList<Foodsearch>();
			while(rs.next()){
				bean = new Foodsearch();
				bean.setFsid(rs.getInt("fsid"));
				bean.setFcid(rs.getString("fcid"));
				bean.setFsname(rs.getString("fsname"));
				result.add(bean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

}
